package saeildamil.ch18.dbconn;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JOptionPane;

public class JdbcEX02 {
	public static void main(String[] args) {
		Connection con = null;
		String message = "데이터베이스 연결에 성공했습니다.";
		try{
		con = dbCon();
		JOptionPane.showMessageDialog(null, message);
		selectDao(con);
		insertDao(con);
		
		selectDao(con);
		
	//	selectwhereDAO(con);
		}finally{
		try {con.close();} catch (Exception e){}
		}
	}

	/*private static void selectwhereDAO(Connection con) {
		Statement stmt = null;
		ResultSet rs = null;
		int code = 0;
		int price =0;
		String name = null; 
		String maker = null;
		GoodsInfoDTO obj = null;
		
		try {
			stmt = con.createStatement();
			String searchName = JOptionPane.showInputDialog("검색하고자 하는 이름을 입력해주세요.");
			String sql = "select code,name,price,maker from goodsinfo where name= '" + searchName + "'";
			// '를 빠뜨리지 말 것(' 후에 ;는 옵션;);
			JOptionPane.showMessageDialog(null, sql);
			rs = stmt.executeQuery(sql);
			System.out.println(rs.getRow());
			//getRow(): row 의 갯수 출력;
			
			// while(rs.next()){
				rs.next();
				// 실제로 저장된 값을 출력하기 위해서는 next()가 있어야... 
				// 그래야 실제 데이터 영역으로 이동해서 값이 있는지 없는지 판단하고, 존재하면 값을 가져오는;
				System.out.println(rs.getRow());
				code = rs.getInt(1);
				name = rs.getString("name");
				price = rs.getInt("price");
				maker = rs.getString(4);
			
			JOptionPane.showMessageDialog(null, "상품코드 = " + code + " 상품명 = " + name + " 가격 = " + price + " 제조사 = " + maker);
//			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}*/

	private static void insertDao(Connection con) {
		Statement stmt = null;
		String name = JOptionPane.showInputDialog("상품이름을 입력해주세요.").trim();
		int price = Integer.parseInt(JOptionPane.showInputDialog("상품의 가격을 입력해주세요.").trim());
		String maker = JOptionPane.showInputDialog("상품의 제조사를 입력해주세요.").trim();
		String sql = "insert into goodsinfo(name,price,maker) values('"+name+"'," + price + ", '" + maker + "')"; 
		JOptionPane.showMessageDialog(null, sql);
		try {
			stmt = con.createStatement();
			int result = stmt.executeUpdate(sql);
			JOptionPane.showMessageDialog(null, "삽입한 결과 : " + result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static void selectDao(Connection con) {
		Statement stmt = null;
		ResultSet rs = null;
		int code = 0, price =0;
		String name = null, maker = null;
		GoodsInfoDTO obj = null;
		ArrayList<GoodsInfoDTO> goodsList = new ArrayList<>();
		String sql;
		try{
		stmt = con.createStatement();
		int num = Integer.parseInt(JOptionPane.showInputDialog("1.결과 검색 2. 조건 검색"));
		if(num==1){
			sql = "select code, name, price, maker from goodsinfo;";
		}else{
			String searchName = JOptionPane.showInputDialog("검색하고자 하는 이름을 입력해주세요.");
			sql = "select code,name,price,maker from goodsinfo where name= '" + searchName + "'";
		}
		rs = stmt.executeQuery(sql);
		while(rs.next()){
			code = rs.getInt(1);
			name = rs.getString("name");
			price = rs.getInt("price");
			maker = rs.getString(4);
			obj = new GoodsInfoDTO(code, name, price, maker);
			goodsList.add(obj);
		}		
		} catch (SQLException e) {
			e.printStackTrace();
	}finally{
		try{
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
		for(GoodsInfoDTO goods : goodsList){
			System.out.println(goods);
		}
	}

	private static Connection dbCon() {
		Connection conn = null;
		BufferedReader bis = null;
		File file = new File("dbinfo.con");
		try {
			bis = new BufferedReader(new FileReader(file));
			String className = bis.readLine();
			String url = bis.readLine();
			String user = bis.readLine();
			String password = bis.readLine();
			Class.forName(className);
			conn = DriverManager.getConnection(url, user, password);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				bis.close();
			} catch (IOException e) {}
		}
		return conn;
	}
}
